CREATE TABLE [dbo].[TBL_FATURA_IADE_ALIS_KODLARI](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BELGEID] [int] NOT NULL,
	[BELGEKODU] [varchar](20) NOT NULL,
	[BELGETARIHI] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_TBL_FATURA_IADE_ALIS_KODLARI] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_TBL_FATURA_IADE_ALIS_KODLARI] ON [dbo].[TBL_FATURA_IADE_ALIS_KODLARI]
(
	[BELGEID] ASC,
	[BELGEKODU] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TBL_FATURA_IADE_ALIS_KODLARI_1] ON [dbo].[TBL_FATURA_IADE_ALIS_KODLARI]
(
	[BELGEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_FATURA_IADE_ALIS_KODLARI]  WITH CHECK ADD  CONSTRAINT [FK_TBL_FATURA_IADE_ALIS_KODLARI_TBL_FATURA_MAIN] FOREIGN KEY([BELGEID])
REFERENCES [dbo].[TBL_FATURA_MAIN] ([ID])
GO

ALTER TABLE [dbo].[TBL_FATURA_IADE_ALIS_KODLARI] CHECK CONSTRAINT [FK_TBL_FATURA_IADE_ALIS_KODLARI_TBL_FATURA_MAIN]
GO

ALTER TRIGGER [dbo].[TRG_FATURA_MAIN_INSTEADOF_DELETE]
   ON  [dbo].[TBL_FATURA_MAIN]
   INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE
		@ERRORMESSAGE		NVARCHAR(4000),
		@BELGEID			INT,
		@BELGEKODU			VARCHAR(20),
		@HAREKETTIPID		INT,
		@DETAYID			INT,
		@STOKADI			VARCHAR(50),
		@ILISKILIBELGEID	INT


	SET @ERRORMESSAGE = ''

	SELECT
		@BELGEID	= ID,
		@BELGEKODU	= BELGEKODU,
		@HAREKETTIPID = HAREKETTIPID
	FROM DELETED

	BEGIN TRANSACTION

		IF @HAREKETTIPID = 3 
		BEGIN
			DECLARE @TALEPID INT

			SELECT 	@TALEPID = ID FROM TBL_FATURA_TALEP_MAIN WITH (NOLOCK)
			WHERE IADETIP = 'F' AND IADEBELGEID = @BELGEID

			IF @TALEPID IS NOT NULL
			BEGIN
				BEGIN TRY
					UPDATE TBL_FATURA_TALEP_MAIN
					SET IADETIP = NULL, IADEBELGEID = NULL
					WHERE ID = @TALEPID
				END TRY
				BEGIN CATCH
					IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

					SELECT
						@ErrorMessage =
								'FATURA TALEP IADEBELGEID UPDATE SIRASINDA HATA OLUŞTU.!'
								+ CHAR(13) + CHAR(10) +
								'->' + ISNULL(ERROR_MESSAGE(), '') +
										'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
										'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

					RAISERROR(@ErrorMessage, 16, 1)
					RETURN
				END CATCH
			END
			ELSE
			BEGIN
				SELECT 	@TALEPID = ID FROM TBL_IRSALIYE_TALEP_MAIN WITH (NOLOCK)
				WHERE IADETIP = 'F' AND IADEBELGEID = @BELGEID
				IF @TALEPID IS NOT NULL
				BEGIN
					BEGIN TRY
						UPDATE TBL_IRSALIYE_TALEP_MAIN
						SET IADETIP = NULL, IADEBELGEID = NULL
						WHERE ID = @TALEPID
					END TRY
					BEGIN CATCH
						IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

						SELECT
							@ErrorMessage =
									'İRSALİYE TALEP IADEBELGEID UPDATE SIRASINDA HATA OLUŞTU.!'
									+ CHAR(13) + CHAR(10) +
									'->' + ISNULL(ERROR_MESSAGE(), '') +
											'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
											'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

						RAISERROR(@ErrorMessage, 16, 1)
						RETURN
					END CATCH
				END
			
			END
		END
		
		
		IF EXISTS(SELECT TOP 1 1 FROM TBL_KASA_FIS_DETAY WITH (NOLOCK) WHERE FATURAID = @BELGEID)
		BEGIN
			DECLARE CURRFK CURSOR FAST_FORWARD FOR
			SELECT BELGEID FROM TBL_KASA_FIS_DETAY WITH (NOLOCK) WHERE FATURAID = @BELGEID
			OPEN CURRFK
			
			FETCH NEXT FROM CURRFK
			INTO @ILISKILIBELGEID

			WHILE @@FETCH_STATUS = 0
			BEGIN
				BEGIN TRY
					DELETE FROM TBL_KASA_FIS_MAIN
					WHERE ID = @ILISKILIBELGEID
				END TRY
				BEGIN CATCH
					CLOSE CURRFK
					DEALLOCATE CURRFK

					IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

					SELECT
						@ErrorMessage =
								'FATURA KASA İLİŞKİ KAYDININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
								+ CHAR(13) + CHAR(10) +
								'->' + ISNULL(ERROR_MESSAGE(), '') +
										'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
										'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

					RAISERROR(@ErrorMessage, 16, 1)
					RETURN
				END CATCH

				FETCH NEXT FROM CURRFK
				INTO @ILISKILIBELGEID
			END
			CLOSE CURRFK
			DEALLOCATE CURRFK
		END
		
		IF EXISTS(SELECT TOP 1 1 FROM TBL_BANKA_FIS_DETAY WITH (NOLOCK) WHERE FATURAID = @BELGEID)
		BEGIN
			DECLARE CURRFK CURSOR FAST_FORWARD FOR
			SELECT BELGEID FROM TBL_BANKA_FIS_DETAY WITH (NOLOCK) WHERE FATURAID = @BELGEID
			OPEN CURRFK
			
			FETCH NEXT FROM CURRFK
			INTO @ILISKILIBELGEID

			WHILE @@FETCH_STATUS = 0
			BEGIN
				BEGIN TRY
					DELETE FROM TBL_BANKA_FIS_MAIN
					WHERE ID = @ILISKILIBELGEID
				END TRY
				BEGIN CATCH
					CLOSE CURRFK
					DEALLOCATE CURRFK

					IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

					SELECT
						@ErrorMessage =
								'FATURA BANKA İLİŞKİ KAYDININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
								+ CHAR(13) + CHAR(10) +
								'->' + ISNULL(ERROR_MESSAGE(), '') +
										'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
										'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

					RAISERROR(@ErrorMessage, 16, 1)
					RETURN
				END CATCH

				FETCH NEXT FROM CURRFK
				INTO @ILISKILIBELGEID
			END
			CLOSE CURRFK
			DEALLOCATE CURRFK
		END

		IF EXISTS(SELECT TOP 1 1 FROM TBL_BANKA_KREDI_KART_ODEME_FIS_MAIN WITH (NOLOCK) WHERE FATURAID = @BELGEID)
		BEGIN
			DECLARE CURRFK CURSOR FAST_FORWARD FOR
			SELECT ID FROM TBL_BANKA_KREDI_KART_ODEME_FIS_MAIN WITH (NOLOCK) WHERE FATURAID = @BELGEID
			OPEN CURRFK
			
			FETCH NEXT FROM CURRFK
			INTO @ILISKILIBELGEID

			WHILE @@FETCH_STATUS = 0
			BEGIN
				BEGIN TRY
					DELETE FROM TBL_BANKA_KREDI_KART_ODEME_FIS_MAIN
					WHERE ID = @ILISKILIBELGEID
				END TRY
				BEGIN CATCH
					CLOSE CURRFK
					DEALLOCATE CURRFK

					IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

					SELECT
						@ErrorMessage =
								'FATURA BANKA KREDI KART ODEME İLİŞKİ KAYDININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
								+ CHAR(13) + CHAR(10) +
								'->' + ISNULL(ERROR_MESSAGE(), '') +
										'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
										'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

					RAISERROR(@ErrorMessage, 16, 1)
					RETURN
				END CATCH

				FETCH NEXT FROM CURRFK
				INTO @ILISKILIBELGEID
			END
			CLOSE CURRFK
			DEALLOCATE CURRFK
		END

		BEGIN TRY
			DELETE FROM TBL_MUHASEBE_FIS_MAIN
			WHERE 
				BELGEID		= @BELGEID AND
				HAREKETTIPID= @HAREKETTIPID 
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA MUHASEBE KAYDININ DETAYININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_ADRES
			WHERE 
				BELGEID		= @BELGEID 
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA ADRES KAYDININ DETAYININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_IADE_ALIS_KODLARI
			WHERE 
				BELGEID		= @BELGEID 
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'ALIS BELGE KODLARININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_BEKLETME
			WHERE 
				FATURAID		= @BELGEID 
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA BEKLETME KAYDININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_MAIN_DEPOZITO
			WHERE 
				BELGEID		= @BELGEID 
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA DEPOZITO KAYDININ DETAYININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		DECLARE CURRDETAY CURSOR FAST_FORWARD FOR
		SELECT
				TSD.ID,
				TSM.STOKADI
		FROM TBL_FATURA_DETAY TSD WITH (NOLOCK)
			INNER JOIN TBL_STOK_MAIN TSM WITH (NOLOCK) ON TSM.ID = TSD.STOKID
		WHERE
			BELGEID = @BELGEID
		OPEN CURRDETAY

		FETCH NEXT FROM CURRDETAY
		INTO @DETAYID, @STOKADI

		WHILE @@FETCH_STATUS = 0
		BEGIN
			BEGIN TRY
				DELETE FROM TBL_FATURA_DETAY
				WHERE ID = @DETAYID
			END TRY
			BEGIN CATCH
				CLOSE CURRDETAY
				DEALLOCATE CURRDETAY
				
				IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

				SELECT
					@ErrorMessage =
							@STOKADI + ' İSİMLİ STOK DETAYININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
							+ CHAR(13) + CHAR(10) +
							'->' + ISNULL(ERROR_MESSAGE(), '') +
									'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
									'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

				RAISERROR(@ErrorMessage, 16, 1)
				RETURN
			END CATCH

			FETCH NEXT FROM CURRDETAY
			INTO @DETAYID, @STOKADI
		END
		CLOSE CURRDETAY
		DEALLOCATE CURRDETAY

		DECLARE CURRDETAY CURSOR FAST_FORWARD FOR
		SELECT
				TSD.ID,
				TSM.MODELADI
		FROM TBL_FATURA_MODEL_DETAY TSD WITH (NOLOCK)
			INNER JOIN TBL_MODEL_MAIN TSM WITH (NOLOCK) ON TSM.ID = TSD.MODELID
		WHERE
			TSD.BELGEID = @BELGEID
		OPEN CURRDETAY

		FETCH NEXT FROM CURRDETAY
		INTO @DETAYID, @STOKADI

		WHILE @@FETCH_STATUS = 0
		BEGIN
			BEGIN TRY
				DELETE FROM TBL_FATURA_MODEL_DETAY
				WHERE ID = @DETAYID
			END TRY
			BEGIN CATCH
				CLOSE CURRDETAY
				DEALLOCATE CURRDETAY
				
				IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

				SELECT
					@ErrorMessage =
							@STOKADI + ' İSİMLİ MODEL DETAYININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
							+ CHAR(13) + CHAR(10) +
							'->' + ISNULL(ERROR_MESSAGE(), '') +
									'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
									'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

				RAISERROR(@ErrorMessage, 16, 1)
				RETURN
			END CATCH

			FETCH NEXT FROM CURRDETAY
			INTO @DETAYID, @STOKADI
		END
		CLOSE CURRDETAY
		DEALLOCATE CURRDETAY

		BEGIN TRY
			DELETE FROM TBL_FATURA_MAIN_MASRAF
			WHERE BELGEID = @BELGEID
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA MASRAFLARININ SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_TALEP_DONUSUM
			WHERE BELGEID = @BELGEID
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA TALEP DÖNÜŞÜMÜNÜN SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

		BEGIN TRY
			DELETE FROM TBL_FATURA_MAIN
			WHERE ID = @BELGEID
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

			SELECT
				@ErrorMessage =
						'FATURA ÜST BİLGİSİNİN SİLİNMESİ SIRASINDA HATA OLUŞTU.!'
						+ CHAR(13) + CHAR(10) +
						'->' + ISNULL(ERROR_MESSAGE(), '') +
								'(Rutin:' + ISNULL(ERROR_PROCEDURE(), '-') + ',' +
								'Satır:' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(), -1)) + ')'

			RAISERROR(@ErrorMessage, 16, 1)
			RETURN
		END CATCH

	IF @@TRANCOUNT > 0 COMMIT TRANSACTION

END
GO


CREATE PROCEDURE SP_FATURA_IADE_ALIS_KODLARI_LIST
	@BELGEID INT
AS
	SELECT * FROM TBL_FATURA_IADE_ALIS_KODLARI WITH (NOLOCK)
	WHERE BELGEID = @BELGEID
	ORDER BY ID ASC
GO

CREATE PROCEDURE SP_FATURA_IADE_ALIS_KODLARI_DELETE
	@BELGEID INT
AS
	DELETE FROM TBL_FATURA_IADE_ALIS_KODLARI 
	WHERE BELGEID = @BELGEID
GO

CREATE PROCEDURE SP_FATURA_IADE_ALIS_KODLARI_INSERT
	@BELGEID		INT,
	@BELGEKODU		VARCHAR(20),
	@BELGETARIHI	SMALLDATETIME
AS
	INSERT INTO TBL_FATURA_IADE_ALIS_KODLARI(BELGEID, BELGEKODU, BELGETARIHI)
	SELECT @BELGEID, @BELGEKODU, @BELGETARIHI
GO
